<?php
/** @package    verysimple::Phreeze */

/**
 * QueryBuilder generates the actual SQL that is executed by Phreezer
 *
 * @package verysimple::Phreeze
 * @author VerySimple Inc.
 * @copyright 1997-2007 VerySimple, Inc.
 * @license http://www.gnu.org/licenses/lgpl.html LGPL
 * @version 2.01
 */
class QueryBuilder
{
    private $_phreezer;
    private $_counter = 0;
    public $Columns;
    public $Tables;
    public $Joins;
    
    /**
     * Constructor
     *
     * @param Phreezer $phreezer
     *          persistance engine
     */
    public function __construct($phreezer)
    {
        $this->_phreezer = & $phreezer;
        $this->Columns = array ();
        $this->Tables = array ();
        $this->Joins = array ();
    }
    
    /**
     * Adds a field map to the queue, which will be used later when the SQL is generated
     *
     * @param FieldMap $fm
     */
    public function AddFieldMap($fm)
    {
        $tablealias = $fm->TableName;
        if (! array_key_exists($tablealias, $this->Tables)) {
            $this->Tables [$tablealias] = $fm->TableName;
        }
            
            // debugging sequence of loading tables
            // print "<div>QueryBuilder->AddFieldMap:" . $tablealias . "-&gt;" . $fm->ColumnName . "</div>";
        
        $this->Columns [$tablealias . "-" . $fm->ColumnName] = $fm->FieldType == FM_CALCULATION ? $fm->ColumnName : "`" . $tablealias . "`.`" . $fm->ColumnName . "` as `" . $fm->ColumnName . "___" . $fm->TableName . "___" . $this->_counter ++ . "`";
    }
    private $_keymapcache = array (); // used to check for recursive eager fetching
    private $_prevkeymap; // used to check for recursive eager fetching
    
    /**
     * Each Model that is to be included in this query is recursed, where all fields are added
     * to the queue and then looks for eager joins to recurse through
     *
     * @param string $typename
     *          name of the Model object to be recursed
     * @param FieldMap $fm
     */
    public function RecurseFieldMaps($typename, $fms)
    {
        
        // if we see the same table again, we have an infinite loop
        if (isset($this->_keymapcache [$typename])) {
            // return; // TODO: why doesn't this work..?
            throw new Exception("A circular EAGER join was detected while parsing `$typename`.  This is possibly due to an EAGER join with `" . $this->_prevkeymap . "`  Please edit your Map so that at least one side of the join is LAZY.");
        }
        
        // first we just add the basic columns of this object
        foreach ($fms as $fm) {
            $this->AddFieldMap($fm);
        }
        
        // get the keymaps for the requested object
        $kms = $this->_phreezer->GetKeyMaps($typename);
        $this->_keymapcache [$typename] = $kms; // save this to prevent infinite loop
        $this->_prevkeymap = $typename;
        
        // each keymap in this object that is eagerly loaded, we want to join into the query.
        // each of these tables, then might have eagerly loaded keymaps as well, so we'll use
        // recursion to eagerly load as much of the graph as is desired
        foreach ($kms as $km) {
            if ($km->LoadType == KM_LOAD_EAGER || $km->LoadType == KM_LOAD_INNER) {
                // check that we didn't eagerly join this already.
                // TODO: use aliases to support multiple eager joins to the same table
                if (isset($this->Joins [$km->ForeignObject . "_is_joined"])) {
                    // print_r($typename);
                    throw new Exception($typename . "Map has multiple EAGER joins to `" . $km->ForeignObject . "` which is not yet supported by Phreeze.");
                }
                
                $ffms = $this->_phreezer->GetFieldMaps($km->ForeignObject);
                
                $this->RecurseFieldMaps($km->ForeignObject, $ffms);
                
                // lastly we need to add the join information for this foreign field map
                $jointype = $km->LoadType == KM_LOAD_INNER ? "inner" : "left";
                
                foreach ($ffms as $ffm) {
                    if (! isset($this->Joins [$ffm->TableName])) {
                        $this->Joins [$ffm->TableName] = " " . $jointype . " join `" . $ffm->TableName . "` on `" . $fms [$km->KeyProperty]->TableName . "`.`" . $fms [$km->KeyProperty]->ColumnName . "` = `" . $ffms [$km->ForeignKeyProperty]->TableName . "`.`" . $ffms [$km->ForeignKeyProperty]->ColumnName . "`";
                    }
                }
                
                // keep track of what we have eagerly joined already
                $this->Joins [$km->ForeignObject . "_is_joined"] = 1;
            }
        }
    }
    
    /**
     * Returns an array of column names that will be included in this query
     *
     * @return string comma-separated list of escaped DB column names
     */
    public function GetColumnNames()
    {
        return implode(", ", array_values($this->Columns));
    }
    
    /**
     * Determines what tables need to be included in the query
     * based on the mapping
     *
     * @param Criteria $criteria
     * @return string "from" sql
     */
    private function GetTableJoinSQL($criteria)
    {
        $sql = "";
        
        $tablenames = array_keys($this->Tables);
        
        if (count($tablenames) > 1) {
            // we're selecting from multiple tables so we have to do an outer join
            $sql .= " from `" . $tablenames [0] . "`";
            
            // WARNING: if tables are being joined in the incorrect order, it is likely
            // caused by a query that goes across more than one foreign relationship.
            // you can force tables to be joined in a specific order by adding a field
            // reference in the outermost Phreezable FieldMap. QueryBuilder will
            // include tables in whatever order they are references, so you can control
            // this by simply adding in referenced to foreign field in the order you want
            // the tables to be joined
            // for ($i = count($tablenames) -1; $i > 0 ; $i--) // this iterates backwards
            for ($i = 1; $i < count($tablenames); $i ++) { // this iterates forwards
                try {
                    $sql .= $this->Joins [$tablenames [$i]];
                } catch (Exception $ex) {
                    // if 'undefined index' occurs here, there is likely a foreign field in the fieldmap that does not have it's related keymap set to KM_LOAD_EAGER
                    throw new Exception("An invalid join was attempted from table '" . $tablenames [$i] . "'. Please verify that the KeyMap fetching strategy for table '" . $tablenames [0] . "' has been properly configured.");
                }
            }
        } else {
            // we are only selecting from one table
            // (LL) added backticks here
            $sql .= " from `" . $tablenames [0] . "` ";
        }
        
        return $sql;
    }
    
    /**
     * Removes the "where" from the beginning of a statement
     *
     * @param
     *          string partial query to parse
     * @return string query without the preceeding "where"
     */
    private function RemoveWherePrefix($sql)
    {
        $sql = trim($sql);
        
        // remove if the query is surrounded by parenths
        while (substr($sql, 0, 1) == "(" && substr($sql, 0, - 1) == ")") {
            $sql = trim(substr($sql, 1, - 1));
        }
        
        while (strtolower(substr($sql, 0, 5)) == "where") {
            $sql = trim(substr($sql, 5));
        }
        
        return $sql;
    }
    
    /**
     * Returns the "where" part of the query based on the criteria parameters
     *
     * @param Criteria $criteria
     * @return String "where" part of the sql query
     */
    private function GetWhereSQL($criteria)
    {
        $ands = $criteria->GetAnds();
        $ors = $criteria->GetOrs();
        
        // TODO: this all needs to move to the criteria object so it will recurse properly ....
        $where = $this->RemoveWherePrefix($criteria->GetWhere());
        
        if (count($ands)) {
            $wdelim = ($where) ? " and " : "";
            foreach ($ands as $c) {
                $tmp = $c->GetWhere();
                $buff = $this->RemoveWherePrefix($tmp);
                if ($buff) {
                    $where .= $wdelim . $buff;
                    $wdelim = " and ";
                }
            }
        }
        
        if (count($ors)) {
            $where = trim($where) ? "(" . $where . ")" : ""; // no primary criteria. kinda strange
            $wdelim = $where ? " or " : "";
            
            foreach ($ors as $c) {
                $tmp = $c->GetWhere();
                $buff = $this->RemoveWherePrefix($tmp);
                if ($buff) {
                    $where .= $wdelim . "(" . $buff . ")";
                    $wdelim = " or ";
                }
            }
        }
        
        // .. end of stuff that should be in criteria
        
        // prepend the "where" onto the statement
        if ($where) {
            $where = " where (" . trim($where) . ") ";
        }
        
        return $where;
    }
    
    /**
     * Builds a SQL statement from the given criteria object and resets the Criteria
     *
     * @param Criteria $criteria
     * @return string fully formed SQL statement
     */
    public function GetSQL($criteria)
    {
        // start building the sql statement
        $sql = "select " . $this->GetColumnNames() . "";
        
        $sql .= $this->GetTableJoinSQL($criteria);
        
        $sql .= $criteria->GetJoin();
        
        $sql .= $this->GetWhereSQL($criteria);
        
        $sql .= $criteria->GetOrder();
        
        $criteria->Reset();
        
        return $sql;
    }
    
    /**
     * Builds a SQL statement from the given criteria object to count the results and resets the Criteria
     *
     * @param Criteria $criteria
     * @return string fully formed SQL statement
     */
    public function GetCountSQL($criteria)
    {
        $sql = "select count(1) as counter ";
        
        $sql .= $this->GetTableJoinSQL($criteria);
        
        $sql .= $criteria->GetJoin();
        
        $sql .= $this->GetWhereSQL($criteria);
        
        $criteria->Reset();
        
        return $sql;
    }
}
